using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Text;

namespace AMStudio.UserInterface
{
    public class PassportAccount
    {
        private DbProviderFactory myfactory;
        private DbConnection myconnection;
        private DbDataAdapter myadapter;
        private DataTable passport;
        public PassportAccount(DbProviderFactory userFactory,string userConnectionString)
        {
            this.myfactory = userFactory;
            this.myadapter = this.myfactory.CreateDataAdapter();
            this.myconnection = this.GetProviderConnection(userConnectionString);
            this.passport = new DataTable();
            this.myadapter.SelectCommand = this.myfactory.CreateCommand();
            this.myadapter.SelectCommand.Connection = this.myconnection;
            this.myadapter.SelectCommand.CommandText = "select * from PassportAccount";
            this.myadapter.Fill(this.passport);
            this.passport.Constraints.Add(new UniqueConstraint(this.passport.Columns["UserName"])); ;
            this.passport.TableNewRow += new DataTableNewRowEventHandler(passport_TableNewRow);
            if (this.myadapter is OleDbDataAdapter)
            {
                this.myadapter.DeleteCommand = new System.Data.OleDb.OleDbCommand();
                this.myadapter.DeleteCommand.Connection = this.myconnection;
                this.myadapter.DeleteCommand.CommandText = "DELETE FROM `PassportAccount` WHERE (`ID` =24D5221C-5FEE-4251-B18E-3B3CA0D450A6) ";
                this.myadapter.DeleteCommand.CommandType = System.Data.CommandType.Text;
                this.myadapter.DeleteCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_ID", System.Data.OleDb.OleDbType.Guid, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "ID", System.Data.DataRowVersion.Original, false, null));
                this.myadapter.InsertCommand = new System.Data.OleDb.OleDbCommand();
                this.myadapter.InsertCommand.Connection = this.myconnection;
                this.myadapter.InsertCommand.CommandText = "INSERT INTO `PassportAccount` (`ID`, `UserName`, `UserPassword`, `Authority`) VAL" +
                    "UES (?, ?, ?, ?)";
                this.myadapter.InsertCommand.CommandType = System.Data.CommandType.Text;
                this.myadapter.InsertCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("ID", System.Data.OleDb.OleDbType.Guid, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "ID", System.Data.DataRowVersion.Current, false, null));
                this.myadapter.InsertCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("UserName", System.Data.OleDb.OleDbType.VarWChar, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "UserName", System.Data.DataRowVersion.Current, false, null));
                this.myadapter.InsertCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("UserPassword", System.Data.OleDb.OleDbType.VarWChar, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "UserPassword", System.Data.DataRowVersion.Current, false, null));
                this.myadapter.InsertCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("Authority", System.Data.OleDb.OleDbType.VarWChar, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "Authority", System.Data.DataRowVersion.Current, false, null));
                this.myadapter.UpdateCommand = new System.Data.OleDb.OleDbCommand();
                this.myadapter.UpdateCommand.Connection = this.myconnection;
                this.myadapter.UpdateCommand.CommandText = @"UPDATE `PassportAccount` SET `ID` = ?, `UserName` = ?, `UserPassword` = ?, `Authority` = ? WHERE ((`ID` = ?) AND ((? = 1 AND `UserName` IS NULL) OR (`UserName` = ?)) AND ((? = 1 AND `UserPassword` IS NULL) OR (`UserPassword` = ?)) AND ((? = 1 AND `Authority` IS NULL) OR (`Authority` = ?)))";
                this.myadapter.UpdateCommand.CommandType = System.Data.CommandType.Text;
                this.myadapter.UpdateCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("ID", System.Data.OleDb.OleDbType.Guid, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "ID", System.Data.DataRowVersion.Current, false, null));
                this.myadapter.UpdateCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("UserName", System.Data.OleDb.OleDbType.VarWChar, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "UserName", System.Data.DataRowVersion.Current, false, null));
                this.myadapter.UpdateCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("UserPassword", System.Data.OleDb.OleDbType.VarWChar, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "UserPassword", System.Data.DataRowVersion.Current, false, null));
                this.myadapter.UpdateCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("Authority", System.Data.OleDb.OleDbType.VarWChar, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "Authority", System.Data.DataRowVersion.Current, false, null));
                this.myadapter.UpdateCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_ID", System.Data.OleDb.OleDbType.Guid, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "ID", System.Data.DataRowVersion.Original, false, null));
                this.myadapter.UpdateCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("IsNull_UserName", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "UserName", System.Data.DataRowVersion.Original, true, null));
                this.myadapter.UpdateCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_UserName", System.Data.OleDb.OleDbType.VarWChar, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "UserName", System.Data.DataRowVersion.Original, false, null));
                this.myadapter.UpdateCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("IsNull_UserPassword", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "UserPassword", System.Data.DataRowVersion.Original, true, null));
                this.myadapter.UpdateCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_UserPassword", System.Data.OleDb.OleDbType.VarWChar, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "UserPassword", System.Data.DataRowVersion.Original, false, null));
                this.myadapter.UpdateCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("IsNull_Authority", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "Authority", System.Data.DataRowVersion.Original, true, null));
                this.myadapter.UpdateCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Authority", System.Data.OleDb.OleDbType.VarWChar, 0, System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "Authority", System.Data.DataRowVersion.Original, false, null));
            }
        }
        private DbConnection GetProviderConnection(string connStr)
        {
            DbConnection connection = this.myfactory.CreateConnection();
            connection.ConnectionString = connStr;
            return connection;
        }
        private void passport_TableNewRow(object sender, DataTableNewRowEventArgs e)
        {
            if (e.Row["ID"] is DBNull)
                e.Row["ID"] = Guid.NewGuid();
        }
        public int Count
        {
            get { return this.passport.Rows.Count; }
        }
        public string this[int index]
        {
            get
            {
                return (string)this.passport.Rows[index]["UserName"];
            }
        }
        public string GetAuthority(int index)
        {
            return (string)this.passport.Rows[index]["Authority"];
        }
        public bool ValidatePassword(string userName, string password)
        {
            DataRow[] dr = this.passport.Select("UserName='" + userName + "'");
            if ((string)dr[0]["UserPassword"] == password)
            {
                return true;
            }
            return false;
        }
        public void AddUser(string userName,string userGroup)
        {
            DataRow myrow = this.passport.NewRow();
            myrow["UserName"] = userName;
            myrow["Authority"] = userGroup;
            myrow["UserPassword"] = "";
            this.passport.Rows.Add(myrow);
        }
        public void RemoveUser(string userName)
        {
            DataRow[] dr = this.passport.Select("UserName='" + userName + "'");
            this.passport.Rows.Remove(dr[0]);
            dr[0].Delete();
        }
        public void ChangePassword(string userName, string newPassword)
        {
            DataRow[] dr = this.passport.Select("UserName='" + userName + "'");
            dr[0]["UserPassword"] = newPassword;
        }
        public void UpdatePassport()
        {
            //DbCommand insertcom = this.myfactory.CreateCommand();
            //insertcom.CommandText = "INSERT INTO PassportAccount (ID, UserName, UserPassword, Authority) VALUES (?, ?, ?, ?)";
            //this.myadapter.InsertCommand = insertcom;
            //DbCommandBuilder builder = this.myfactory.CreateCommandBuilder();
            //builder.DataAdapter = this.myadapter;
            //this.myadapter.InsertCommand = builder.GetInsertCommand();
            //this.myadapter.DeleteCommand = builder.GetDeleteCommand();
            //this.myadapter.UpdateCommand = builder.GetUpdateCommand();
            this.myadapter.Update(this.passport);
            this.passport.AcceptChanges();

        }
    }
}
